Keyboard shortcuts

Press ← or → to navigate between chapters

Press S or / to search in the book

Press ? to show this help

Press Esc to hide this help

What are N plus 1 queries

This happens a lot in Rails and other ORMs. Conceptually we have a master object, something like an Order (or Project) and it has other data hanging from it. Instead of pulling the data in one hit from the database we will get our list of orders, and then start pulling all the detail records for it one row at a time. You’re doing one query to get the list of orders, and then for each row another to get the details, hence N + 1, it should perhaps be 1 + N to be pedantic about it - as in one query gets you N queries.

This doesn’t sound too bad, the detail queries are probably using indexes and relatively quick to run, but you might have hundreds of them.

Think about how the query result set turned into something you can access as an object:

In this example also think about how an order might also have many products that need rendering, then you have (N + 1 ) + 1 queries to the database.

Marshalling is taking the data and turning it into objects, it’s also sometimes called serialisation, turning the data into a stream that can be transmitted from the database to the Rails stack over an API using a socket.

It’s easy to create an N + 1 in Rails, because using Active Record you just pull the main query, and the subqueries happen when you access the child data. The code looks fine and renders ok. If you fix them the rendering code looks the same, but the data comes from the database in one hit. You may also need to use outer joins to get things. If data is missing in a more complex query you might find yourself not seeing rows that you should.

Fixing them

In Rails there’s a gem called Bullet that will warn you if there seems to be an N + 1 happening in a view render. You can also ask a friendly AI to identify some, but sometimes they get confused and call something an N + 1 when it is not. Even without Bullet you can usually see issues like this in the development log. You’ll see the query for customers, and then a pile of queries once for each order.

Let the database do as much work as possible and get all of the data you need.

  1. Use includes and joins to fetch the child data along with the order data
  2. Marshall all of the data you need to render in one hit
  3. No more back and forth to the database

It sounds trivial, and it kind of is, but you need to look really carefully at the nesting of queries to work out when it’s needed.

Let’s explore the customers and orders tables some more:

There is a directory called ruby-examples in the repository, the Readme explains how to set things up so it will run. It’s also configured to give logging as it runs.

First, let’s add some customers and orders

3.times do |i|
  customer = Customer.create!(customer_name: "Customer #{i + 1}")
  2.times do |j|
    Order.create!(customer: customer, order_date: Date.new(2026, 8, j + 1))
  end
end

Now trigger the N + 1

customers = Customer.all
customers.each do |customer|
  puts "#{customer.customer_name}: #{customer.orders.count}"
end

This gives us a log like this:

D, [2026-08-03T13:43:35.575054 #52735] DEBUG -- :   Customer Load (0.6ms)  SELECT "customers".* FROM "customers"
D, [2026-08-03T13:43:35.576622 #52735] DEBUG -- :   Order Count (0.8ms)  SELECT COUNT(*) FROM "orders" WHERE "orders"."customer_id" = $1  [["customer_id", 1]]
Acme Industries: 1
D, [2026-08-03T13:43:35.577016 #52735] DEBUG -- :   Order Count (0.2ms)  SELECT COUNT(*) FROM "orders" WHERE "orders"."customer_id" = $1  [["customer_id", 2]]
Customer 1: 2
D, [2026-08-03T13:43:35.577349 #52735] DEBUG -- :   Order Count (0.2ms)  SELECT COUNT(*) FROM "orders" WHERE "orders"."customer_id" = $1  [["customer_id", 3]]
Customer 2: 2
D, [2026-08-03T13:43:35.577631 #52735] DEBUG -- :   Order Count (0.2ms)  SELECT COUNT(*) FROM "orders" WHERE "orders"."customer_id" = $1  [["customer_id", 4]]
Customer 3: 2

As you can see, the customers are loaded first, and then each order one at a time.

Now, let’s stop the N + 1 by including the orders, note that we’re using size instead of count because we’ve retrieved the array and don’t want to trigger a count in Active Record:

customers = Customer.includes(:orders).to_a
customers.each do |customer|
  puts "#{customer.customer_name}: #{customer.orders.size}"
end

When interleaved with the log output it looks like this:

irb(main):001> customers = Customer.includes(:orders)
D, [2026-08-03T13:51:28.614437 #77991] DEBUG -- :   Customer Load (0.4ms)  SELECT "customers".* FROM "customers" /* loading for pp */ LIMIT $1  [["LIMIT", 11]]
D, [2026-08-03T13:51:28.632245 #77991] DEBUG -- :   Order Load (0.5ms)  SELECT "orders".* FROM "orders" WHERE "orders"."customer_id" IN ($1, $2, $3, $4)  [["customer_id", 1], ["customer_id", 2], ["customer_id", 3], ["customer_id", 4]]
=> [#<Customer:0x0000000125d6d550 customer_id: 1, customer_name: "Acme Industries">, #<Customer:0x0000000125c48490 customer_id: 2, customer_name: "Customer 1">, #<Customer:0x0000000125c48350 customer_id: 3, customer_name: "Customer 2">, #<Customer:0x0000000125c48210 customer_id: 4, customer_name: "Customer 3">]
irb(main):002* customers.each do |customer|
irb(main):003*   puts "#{customer.customer_name}: #{customer.orders.size}"
irb(main):004> end
D, [2026-08-03T13:51:31.532422 #77991] DEBUG -- :   Customer Load (0.6ms)  SELECT "customers".* FROM "customers"
D, [2026-08-03T13:51:31.533314 #77991] DEBUG -- :   Order Load (0.3ms)  SELECT "orders".* FROM "orders" WHERE "orders"."customer_id" IN ($1, $2, $3, $4)  [["customer_id", 1], ["customer_id", 2], ["customer_id", 3], ["customer_id", 4]]
Acme Industries: 1
Customer 1: 2
Customer 2: 2
Customer 3: 2

Now you can see that ActiveRecord pulls all of the customers and all the related orders before processing. This means that the database (and the Postgres API) isn’t being hammered by lots of small requests.

Note that you can also stop N + 1 + 1 (whatever that’s called) with constructs like

Customer.includes(:orders, { orders: :order_lines } )
D, [2026-08-03T14:50:20.585853 #77991] DEBUG -- :   Customer Load (0.5ms)  SELECT "customers".* FROM "customers" /* loading for pp */ LIMIT $1  [["LIMIT", 11]]
D, [2026-08-03T14:50:20.588160 #77991] DEBUG -- :   Order Load (0.4ms)  SELECT "orders".* FROM "orders" WHERE "orders"."customer_id" IN ($1, $2, $3, $4)  [["customer_id", 1], ["customer_id", 2], ["customer_id", 3], ["customer_id", 4]]
D, [2026-08-03T14:50:20.595203 #77991] DEBUG -- :   OrderLine Load (0.9ms)  SELECT "order_lines".* FROM "order_lines" WHERE "order_lines"."order_id" IN ($1, $2, $3, $4, $5, $6, $7)  [["order_id", 1], ["order_id", 2], ["order_id", 3], ["order_id", 4], ["order_id", 5], ["order_id", 6], ["order_id", 7]]